﻿--1
SELECT 
	AREA_MGR, 
	AREA_MGR "ACCT_MGR", 
	1 LVL, 
	1 ORD 
FROM 
	SUNRISE_TEST.ACC_BASE_DATA 
GROUP BY AREA_MGR 
UNION 
SELECT 
	'Southeast',
	'Steve Shaw',  
	0 LVL, 
	0 ORD 
FROM 
	SUNRISE_TEST.ACC_BASE_DATA 
UNION 
SELECT 
	AREA_MGR, 
	ACCT_MGR, 
	2 LVL, 
	1 ORD 
FROM 
	SUNRISE_TEST.ACC_BASE_DATA 
ORDER BY 4,1,3,2
;

--2
SELECT 
	B.STATE_NM TXT, 
	SUBSTR(A.TRAC_ID,1,2) VAL 
FROM 
	SUNRISE_TEST.ACC_BASE_DATA 
	A 
	INNER JOIN 
	VEDWEN.STATELIST B 
	ON 
	SUBSTR(A.TRAC_ID,1,2)=B.STATE_CD 
GROUP BY 1,2 
UNION ALL 
SELECT 
	' ALL' TXT, 
	'ZZ' VAL 
FROM 
	SYS_CALENDAR.CALENDAR 
WHERE 
	CALENDAR_DATE=CURRENT_DATE 
ORDER BY 1
;

--3
SELECT 
	PORTFOLIO TXT, 
	PORTFOLIO VAL 
FROM 
	SUNRISE_TEST.ACC_BASE_DATA 
GROUP BY 1 
ORDER BY 1
;

--4
SELECT 
	CASE MARKET 
	WHEN 'ALM' THEN 'ALABAMA-LOUISIANA-MISSISSIPPI' 
	WHEN 'FL' THEN 'FLORIDA' 
	WHEN 'GA' THEN 'GEORGIA' 
	WHEN 'KTNS' THEN 'KENTUCKY-TENNESSEE-CAROLINAS' 
	END TXT, 
	MARKET VAL 
FROM 
	SUNRISE_TEST.ACC_BASE_DATA 
GROUP BY 1,2 
ORDER BY 1
;